﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

using Business;

namespace Data
{

    public class DataComentario
    {
        public DataComentario()
        {
        }

        public String getUsername(int userID)
        {
            Connect c = new Connect();
            String username = "";
            try
            {
                SqlConnection conn = c.connect("");
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT username FROM Utilizador WHERE idUtilizador=" + userID;
                SqlDataReader leitor = cmd.ExecuteReader();
                if (leitor.Read())
                {
                    if(!leitor.IsDBNull(0))
                        username = Convert.ToString(leitor[0]);
                }
                leitor.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            return username;
        }


        public Comentario getComentarioByID(int commentID)
        {
            Connect c = new Connect();
            Comentario comment = new Comentario();
            try
            {
                SqlConnection conn = c.connect("");
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM EventoComentario WHERE idComentario=" + commentID;

                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    String commentText = "";
                    DateTime data = new DateTime();

                    int commentarioID = Convert.ToInt32(reader[2]);
                    int eventID = Convert.ToInt16(reader[0]);
                    int userID = Convert.ToInt16(reader[1]);
                    if(!reader.IsDBNull(3))
                        commentText = Convert.ToString(reader[3]);
                    if (!reader.IsDBNull(4))
                        data = Convert.ToDateTime(reader[4]);
                    int status = Convert.ToInt16(reader[5]);
                    String username = getUsername(userID);

                    comment = new Comentario(eventID, userID, username, commentText, data);
                    comment.setComentarioID(commentarioID);
                    comment.setComentarioStatus(status);
                }


            }

            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            return comment;
        }

        public ListaComentario getListaComentariosUser(int eventoID, int status, int userID)
        {
            Comentario com = null;
            Connect c = new Connect();
            ListaComentario lc = new ListaComentario();
            try
            {
                SqlConnection conn = c.connect("");
                SqlCommand command = conn.CreateCommand();

                if (status == -10)
                    command.CommandText = "Select * from EventoComentario where Evento_idEvento=" + eventoID + " and Utilizador_idUtilizador=" + userID;
                else
                    command.CommandText = "Select * from EventoComentario where Evento_idEvento=" + eventoID + " and status=" + status + "and Utilizador_idUtilizador=" + userID;
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    String commentText = "";
                    DateTime data = new DateTime();

                    int commentarioID = Convert.ToInt32(reader[2]);
                    int eventID = Convert.ToInt16(reader[0]);
                    int userid = Convert.ToInt16(reader[1]);
                    if (!reader.IsDBNull(3))
                        commentText = Convert.ToString(reader[3]);
                    if (!reader.IsDBNull(4))
                        data = Convert.ToDateTime(reader[4]);
                    int statuss = Convert.ToInt16(reader[5]);
                    String username = getUsername(userID);

                    com = new Comentario(eventID, userid, username, commentText, data);
                    com.setComentarioID(commentarioID);
                    com.setComentarioStatus(statuss);
                    lc.addComment(com);
                }
                conn.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }

            return lc;
        }

        public ListaComentario getListaComentarios(int eventoID, int status)
        {
            Comentario com = null;
            Connect c = new Connect();
            ListaComentario lc = new ListaComentario();
            try
            {
                SqlConnection conn = c.connect("");
                SqlCommand command = conn.CreateCommand();

                if (status == -10)
                    command.CommandText = "Select * from EventoComentario where Evento_idEvento=" + eventoID;
                else
                    command.CommandText = "Select * from EventoComentario where Evento_idEvento=" + eventoID + " and status=" + status;
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    String commentText = "";
                    DateTime data = new DateTime();

                    int commentarioID = Convert.ToInt32(reader[2]);
                    int eventID = Convert.ToInt16(reader[0]);
                    int userID = Convert.ToInt16(reader[1]);
                    if (!reader.IsDBNull(3))
                        commentText = Convert.ToString(reader[3]);
                    if (!reader.IsDBNull(4))
                        data = Convert.ToDateTime(reader[4]);
                    int statuss = Convert.ToInt16(reader[5]);
                    String username = getUsername(userID);

                    com = new Comentario(eventID, userID, username, commentText, data);
                    com.setComentarioID(commentarioID);
                    com.setComentarioStatus(statuss);
                    lc.addComment(com);
                }
                conn.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }

            return lc;

        }



        public String insertComentario(Comentario c)
        {
            int value = -1;
            String str = "";
            Connect con = new Connect();
            try
            {
                SqlConnection conn = con.connect("");
                SqlCommand command = conn.CreateCommand();

                command.CommandText = "INSERT INTO EventoComentario(Evento_idEvento,Utilizador_idUtilizador,comentario,status) VALUES " + " (" + c.getComentarioEventoID() + "," + c.getComentarioUtilizadorID() + ",'" + c.getComentarioText() + "',1)";
                str = "INSERT INTO EventoComentario(Evento_idEvento,Utilizador_idUtilizador,comentario,status) VALUES " + " (" + c.getComentarioEventoID() + "," + c.getComentarioUtilizadorID() + ",'" + c.getComentarioText() + "',1)";
                int i = command.ExecuteNonQuery();

                command.CommandText = "SELECT MAX(idComentario) FROM EventoComentario";
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                    value = Convert.ToInt16(reader[0]);
                reader.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }

            return str;
        }




        public bool setEstadoComentario(Comentario c, int novoEstado)
        {
            bool value = false;
            Connect con = new Connect();
            try
            {
                SqlConnection conn = con.connect("");
                SqlCommand command = conn.CreateCommand();

                command.CommandText = "UPDATE EventoComentario SET status=" + novoEstado + " WHERE (idComentario=" + c.getComentarioID() + ")";
                int i = command.ExecuteNonQuery();
                if (i > 0) value = true;
                conn.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            return value;
        }


    }

}